/*
 * PKL 51 GUNUNG KIDUL
 * Subseksie DATA ENTRY + CAPI
 */
package pkl51.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import pkl51.database.DatabaseUtility;
import pkl51.entity.HomeEntity;
import pkl51.error.EntityException;
import pkl51.service.HomeDao;

/**
 *
 * @author FITRI ANDRI ASTUTI
 */
public class HomeDaoImpl implements HomeDao{
    private Connection connection;
    private final String getHomeByNimPencacah = "SELECT k.nama_kec, d.nama_desa, bs.no_bs, r.no_urut_bs, r.B1R6, r.NKS, r.B1R8, r.B1R9  FROM ruta r, kecamatan k , blok_sensus bs, desa d WHERE r.kode_kec=k.kode_kec AND r.no_urut_bs=bs.no_urut_bs AND r.kode_kec=bs.kode_kec AND bs.kode_desa=d.kode_desa AND d.kode_kec=r.kode_kec AND r.nim_pencacah=?";
    
    @Override
    public List<HomeEntity> getHomeByNimPencacah(String nimPencacah) throws EntityException {
        PreparedStatement statement = null;
        try {
            connection = DatabaseUtility.getConnection();
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(getHomeByNimPencacah);
            statement.setString(1, nimPencacah);

            ResultSet result = statement.executeQuery();
            List<HomeEntity> list = new ArrayList<HomeEntity>();
            
            while (result.next()) {
                HomeEntity homeEntity = new HomeEntity();
                homeEntity.setNama_kec(result.getString("nama_kec")); 
                homeEntity.setNama_desa(result.getString("nama_desa")); 
                homeEntity.setNo_bs(result.getString("no_bs"));
                homeEntity.setNo_urut_bs(result.getString("no_urut_bs"));
                homeEntity.setB1R6(result.getString("B1R6"));
                homeEntity.setNKS(result.getString("NKS"));
                homeEntity.setB1R8(result.getString("B1R8"));
                homeEntity.setB1R9(result.getString("B1R9"));
                list.add(homeEntity);
            } 
            connection.commit();
            return list;
        } catch (SQLException exception) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, exception);
            throw new EntityException(exception.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException exception) {
                    Logger.getLogger(Blok2DaoImpl.class.getName()).log(Level.SEVERE, null, exception);
                }
            }
        }
    }
}
